<!DOCTYPE html><html><head><title>SQL性能优化-索引篇</title><meta charset='utf-8'><link href='https://cdn.maxiang.io/res-min/themes/marxico.css' rel='stylesheet'><style></style></head><body><div id='preview-contents' class='note-content'>
                        
                    

<h1 id="sql性能优化-索引篇">SQL性能优化-索引篇</h1>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#sql性能优化-索引篇">SQL性能优化-索引篇</a><ul>
<li><a href="#视频路径">视频路径</a></li>
<li><a href="#mysql-搜索引擎-常见两种">MySQL 搜索引擎 (常见两种)</a><ul>
<li><a href="#myisam">MyISAM</a></li>
<li><a href="#innodb">InnoDB</a></li>
</ul>
</li>
<li><a href="#常见优化规则">常见优化规则</a></li>
<li><a href="#分析查询日志和慢查询日志">分析查询日志和慢查询日志</a></li>
<li><a href="#表索引原理">表索引原理</a></li>
<li><a href="#索引类型">索引类型</a></li>
<li><a href="#索引方法">索引方法</a></li>
</ul>
</li>
</ul>
</div>
</div>
</div>



<h2 id="视频路径">视频路径</h2>

<pre class="prettyprint hljs-dark"><code class="hljs awk"><div class="hljs-line">https:<span class="hljs-regexp">//</span>ke.qq.com<span class="hljs-regexp">/webcourse/i</span>ndex.html<span class="hljs-comment">#cid=1910186&amp;term_id=102011049&amp;taid=8028672562636202&amp;vid=5285890800612726163</span>
</div></code></pre>



<h2 id="mysql-搜索引擎-常见两种">MySQL 搜索引擎 (常见两种)</h2>



<h3 id="myisam">MyISAM</h3>



<pre class="prettyprint hljs-dark"><code class="hljs sql"><div class="hljs-line">它不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以
</div><div class="hljs-line"><span class="hljs-keyword">SELECT</span>、<span class="hljs-keyword">INSERT</span>为主的应用基本都可以使用这个引擎来创建表。
</div></code></pre>



<pre class="prettyprint hljs-dark"><code class="hljs gcode"><div class="hljs-line">MyISAM的表还支持<span class="hljs-number">3</span>种不同的存储格式：
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">静态<span class="hljs-comment">(固定长度)</span>表
</div><div class="hljs-line">动态表
</div><div class="hljs-line">压缩表
</div></code></pre>



<h3 id="innodb">InnoDB</h3>



<pre class="prettyprint hljs-dark"><code class="hljs"><div class="hljs-line">InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，
</div><div class="hljs-line">InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
</div></code></pre>

<pre class="prettyprint hljs-dark"><code class="hljs lsl"><div class="hljs-line"><span class="hljs-number">1</span>、自动增长列
</div><div class="hljs-line"><span class="hljs-number">2</span>、外键约束
</div></code></pre>

<h2 id="常见优化规则">常见优化规则</h2>

<pre class="prettyprint hljs-dark"><code class="hljs sql"><div class="hljs-line">单条查询最后添加 LIMIT 1，停止全表扫描。
</div><div class="hljs-line">将非”索引”数据分离，比方将大篇文章分离存储，不影响其它自己主动查询。
</div><div class="hljs-line">不用 MYSQL 内置的函数。由于内置函数不会建立查询缓存。
</div><div class="hljs-line">PHP 的建立连接速度很快，全部能够不用连接池。否则可能会造成超过连接数。当然不用连接池
</div><div class="hljs-line">PHP程序也可能将
</div><div class="hljs-line">连接数占满比方用了 @ignore_user_abort(TRUE);
</div><div class="hljs-line">使用 IP 而不是域名做数据库路径。避免 DNS 解析问题
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">连接的表越多，性能越差
</div><div class="hljs-line">可能的话，将连接拆分成若干个过程逐一执行
</div><div class="hljs-line">优先执行可显著减少数据量的连接，既降低了复杂度，也能够容易按照预期执行
</div><div class="hljs-line">如果不可避免多表连接，很可能是设计缺陷
</div><div class="hljs-line">外链接效果差，因为必须对左右表进行表扫描
</div><div class="hljs-line">尽量使用inner join查询
</div><div class="hljs-line">不要使用<span class="hljs-keyword">select</span> * ,写明具体查询字段
</div><div class="hljs-line">模糊匹配<span class="hljs-keyword">like</span> 通配符出现在首位，无法使用索引，反之可以
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line"><span class="hljs-comment">-- 无法使用索引</span>
</div><div class="hljs-line"><span class="hljs-keyword">select</span> .. <span class="hljs-keyword">from</span> .. <span class="hljs-keyword">where</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'%t%'</span> ;
</div><div class="hljs-line"><span class="hljs-comment">-- 可以使用索引</span>
</div><div class="hljs-line"><span class="hljs-keyword">select</span> .. <span class="hljs-keyword">from</span> .. <span class="hljs-keyword">where</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'t%'</span> ;
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">order by子句中不要使用非索引列或嵌套表达式，这样都会导致性能降低
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line"> Not运算
</div></code></pre>



<pre class="prettyprint hljs-dark"><code class="hljs sql"><div class="hljs-line">not运算无法使用索引，可以改成其他能够使用索引的操作。如下：
</div><div class="hljs-line"><span class="hljs-comment">-- 索引无效</span>
</div><div class="hljs-line"><span class="hljs-keyword">select</span> .. <span class="hljs-keyword">from</span> .. <span class="hljs-keyword">where</span> sal != <span class="hljs-number">3000</span> ;
</div><div class="hljs-line"><span class="hljs-comment">-- 索引生效</span>
</div><div class="hljs-line"><span class="hljs-keyword">select</span> .. <span class="hljs-keyword">from</span> .. <span class="hljs-keyword">where</span> sal &lt; <span class="hljs-number">3000</span>  <span class="hljs-keyword">or</span> sal &gt; <span class="hljs-number">3000</span>;
</div></code></pre>

<pre class="prettyprint hljs-dark"><code class="hljs applescript"><div class="hljs-line"> <span class="hljs-keyword">where</span>与having
</div></code></pre>



<pre class="prettyprint hljs-dark"><code class="hljs sql"><div class="hljs-line"><span class="hljs-keyword">select</span> .. <span class="hljs-keyword">from</span> .. <span class="hljs-keyword">on</span> .. <span class="hljs-keyword">where</span> .. <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> .. <span class="hljs-keyword">having</span> .. <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> .
</div><div class="hljs-line"><span class="hljs-keyword">limit</span> ..，以上是<span class="hljs-keyword">sql</span>语句的语法结构，其中<span class="hljs-keyword">on</span>、<span class="hljs-keyword">where</span>和<span class="hljs-keyword">having</span>是有过滤行
</div><div class="hljs-line">为的，过滤行为越能提前完成就越可以减少传递给下一个阶段的数据量，因
</div><div class="hljs-line">此如果在<span class="hljs-keyword">having</span>中的过滤行为能够在<span class="hljs-keyword">where</span>中完成，则应该优先考虑<span class="hljs-keyword">where</span>来实现。
</div></code></pre>

<pre class="prettyprint hljs-dark"><code class="hljs ceylon"><div class="hljs-line"> <span class="hljs-keyword">exists</span>替代<span class="hljs-keyword">in</span>
</div></code></pre>



<pre class="prettyprint hljs-dark"><code class="hljs vbnet"><div class="hljs-line"><span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span>是最低效的，因为要对子查询的表进行全表扫描。可以考虑使用外链接或<span class="hljs-keyword">not</span> exists。如下：
</div><div class="hljs-line">-- 正确
</div><div class="hljs-line"><span class="hljs-keyword">SELECT</span>  *
</div><div class="hljs-line"><span class="hljs-keyword">FROM</span> EMP 
</div><div class="hljs-line"><span class="hljs-keyword">WHERE</span>  
</div><div class="hljs-line">    EMPNO &gt; <span class="hljs-number">0</span> 
</div><div class="hljs-line">    <span class="hljs-keyword">AND</span>  EXISTS (<span class="hljs-keyword">SELECT</span> ‘X<span class="hljs-comment">' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')</span>
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">-- 错误
</div><div class="hljs-line"><span class="hljs-keyword">SELECT</span>  * 
</div><div class="hljs-line"><span class="hljs-keyword">FROM</span>  EMP 
</div><div class="hljs-line"><span class="hljs-keyword">WHERE</span>  EMPNO &gt; <span class="hljs-number">0</span>  <span class="hljs-keyword">AND</span>  DEPTNO <span class="hljs-keyword">IN</span>(<span class="hljs-keyword">SELECT</span> DEPTNO  <span class="hljs-keyword">FROM</span>  DEPT  <span class="hljs-keyword">WHERE</span>  LOC = ‘MELB<span class="hljs-comment">')</span>
</div></code></pre>



<h2 id="分析查询日志和慢查询日志">分析查询日志和慢查询日志</h2>

<pre class="prettyprint hljs-dark"><code class="hljs sql"><div class="hljs-line">记录全部查询。这在用 ORM 系统或者生成查询语句的系统非常实用。
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">log=/var/log/mysql.log
</div><div class="hljs-line">注意不要在生产环境用。否则会占满你的磁盘空间。
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">记录运行时间超过 1 秒的查询：
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">long_query_time=1
</div><div class="hljs-line">log-slow-queries=/var/log/mysql/log-slow-queries.log   
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">1 <span class="hljs-comment">-- 查询mysql服务器的连接次数</span>
</div><div class="hljs-line"> <span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">STATUS</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'Connections'</span>;
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">4 <span class="hljs-comment">-- 查询mysql服务器的慢查询次数。慢查询次数参数可以结合慢查询日志，找出慢查询语句，</span>
</div><div class="hljs-line">然后针对慢查询语句进行表结构优化或者查询语句优化。
</div><div class="hljs-line"> <span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">STATUS</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'Slow_queries'</span>;
</div></code></pre>



<h2 id="表索引原理">表索引原理</h2>



<pre class="prettyprint hljs-dark"><code class="hljs"><div class="hljs-line">数据库索引，是数据库管理系统（DBMS）中一个排序的 
</div><div class="hljs-line">数据结构,以协助快速查询、更新数据库表中的数据。
</div></code></pre>



<h2 id="索引类型">索引类型</h2>

<p>### Normal (普通索引) <br>
  ### Unique (唯一索引) <br>
  ### Fulltext (全文索引)</p>



<h2 id="索引方法">索引方法</h2>

<p>### BTREE (B + Tree(树的深度 2))</p>

<pre class="prettyprint hljs-dark"><code class="hljs armasm"><div class="hljs-line"><span class="hljs-keyword">B-Tree对索引列是顺序组织存储的，索引很适合查找范围数据。</span>
</div><div class="hljs-line"><span class="hljs-keyword"></span>
</div><div class="hljs-line"><span class="hljs-keyword">B-Tree索引的限制</span>
</div><div class="hljs-line"><span class="hljs-keyword"></span>如果不是按照索引的最左列开始查找，则无法使用索引。
</div><div class="hljs-line">不能跳过索引中的列
</div><div class="hljs-line">如果查询中有某列的范围查询，则其右边所有列都无法使用索引优化查询。
</div><div class="hljs-line">这些限制都和索引列的顺序存储有关系。或者说是索引顺序存储导致了这些限制。
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">度（Degree [d gu rui]）: N + <span class="hljs-number">1</span>   多叉树 以及 分叉树
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line"><span class="hljs-keyword">B </span>+ Tree(树的深度 <span class="hljs-number">2</span>) 的特点
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">链表的数据结构、从叶子节点拿到数据
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line"><span class="hljs-number">1</span>、扫库、扫表能力更强、减少了IO操作
</div><div class="hljs-line"><span class="hljs-number">2</span>、磁盘读写能力强
</div><div class="hljs-line"><span class="hljs-number">3</span>、排序能力更强
</div><div class="hljs-line"><span class="hljs-number">4</span>、效力更加稳定
</div></code></pre>

<p>### HASH(哈希)</p>

<pre class="prettyprint hljs-dark"><code class="hljs coq"><div class="hljs-line">哈希索引基于哈希表实现的，只有精确匹配索引所有列的查询才有效。
</div><div class="hljs-line">范围查询、<span class="hljs-built_in">in</span> 等不支持
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">哈希索引限制：
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用<span class="hljs-string">"覆盖索引"</span>的优化方式，
</div><div class="hljs-line">去避免读取数据表。
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">哈希索引数据并不是按照索引值顺序存储的，索引也就无法用于排序
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">哈希索引页不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容计算哈希值的。
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">哈希索引只支持等值比较查询，包括=，<span class="hljs-built_in">in</span>(),&lt;=&gt;，不支持任何范围查询。列入<span class="hljs-keyword">where</span> price&gt;<span class="hljs-number">100</span>
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">如果哈希冲突很多的话，一些索引维护操作的代价也会很高。
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">innodb引擎有一个特殊的功能“自适应哈希索引”，当innodb注意到一些索引值被使用的非常频繁时，
</div><div class="hljs-line">且符合哈希特点（如每次查询的列都一样），它会在内存中基于
</div><div class="hljs-line">B-Tree索引之上再创建一个哈希索引。这是一个完全自动的，内部行为。
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">创建自定义哈希索引，像模拟innodb一样创建哈希索引。
</div><div class="hljs-line">例如只需要很小的索引就可以为超长的键创建索引。
</div><div class="hljs-line"><wbr>
</div><div class="hljs-line">思路：在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree
</div><div class="hljs-line">进行查找，但是它使用哈希值而不是键本身进行索引查找。需要做的就是在查询的<span class="hljs-keyword">where</span> 子句中手动
</div><div class="hljs-line">指定使用哈希函数。
</div></code></pre></div></body></html>